#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )

import csv,time,datetime
import redis
import MySQLdb
import pandas as pd
import numpy as np
import statsmodels.api as sm
import json

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)

r = redis.Redis(host='localhost',port=6379,db=0)

bi_conn = MySQLdb.connect(
    host = '172.16.221.68',
    port = 3306,
    user = 'wanglei',
    passwd = 'wl@123',
    db = 'Databehavior',
    charset = 'utf8',
    )

cms_conn = MySQLdb.connect(
    host = '172.16.221.62',
    port = 3306,
    user = 'wanglei',
    passwd = 'wl@123',
    db = 'golivecms20',
    charset = 'utf8',
    )

dayboxoffice_bi_sql = """
SELECT ReportDate, SUM(Amount) as boxoffice  FROM `t_movieticketingstatistics`
WHERE ReportDate>="2015-05-26"
GROUP BY ReportDate
ORDER BY ReportDate
DESC;
"""

cash_cms_sql = """
SELECT date_format(pay_time,'%Y-%m-%d') as t,SUM(price) as cash FROM `t_order` 
WHERE pay_time >= "2015-05-26 00:00:00" AND 
      pay_time < CURDATE() AND 
      product_type = 0
GROUP BY t
ORDER BY t
DESC;
"""

increase_bi_sql = """
SELECT ReportTime, sum(Increase) as increase
    FROM t_memberstatistics
    WHERE ReportTime >= '2015-05-26' AND 
          ReportTime < CURDATE()  
    GROUP BY ReportTime 
    ORDER BY ReportTime DESC
"""

def _get_predict(df,columns_list):
    predict_list = [today.isoformat()]
    index = pd.date_range("2015-5-26", periods=len(df), freq="D")
    for column in columns_list: 
        if column!="ReportDate":
            endog = pd.TimeSeries(df[column].as_matrix(), index=index)
            ar_model = sm.tsa.AR(endog,freq="A")
            pandas_ar_res = ar_model.fit(max_lag=50)
            pred = pandas_ar_res.predict(len(df),len(df),dynamic=False)
            predict_list.append(int(pred[pred.index[0]]))
    return predict_list

def get_history_vip():
    bi_df = pd.read_sql(dayboxoffice_bi_sql, bi_conn)
    cms_df = pd.read_sql(cash_cms_sql, cms_conn)
    bi_df.insert(2,"cash",cms_df["cash"]) 
    in_df = pd.read_sql(increase_bi_sql, bi_conn)
    bi_df.insert(3,"increase",in_df["increase"])
    bi_df.loc[-1] = _get_predict(bi_df.sort("ReportDate",ascending=True),list(bi_df.columns))
    bi_df.index = bi_df.index + 1
    bi_df = bi_df.sort_index(ascending=True)
    bi_df = bi_df[0:(today - datetime.date(2016,01,01)).days+1]   
    bi_df_m = bi_df[0:(today - datetime.date(2016,02,01)).days+1] 
    bi_df = bi_df.sort_index(ascending=False)
    bi_df_m = bi_df_m.sort_index(ascending=False)
    bi_df["boxoffice"] = bi_df["boxoffice"]/100  
    bi_df_m["boxoffice"] = bi_df_m["boxoffice"]/100 

    total = bi_df[1:].sum()
    kpi_boxoffice_total = total.boxoffice
    kpi_cash_total = total.cash
    kpi_increase_total = int(total.increase)

    total_m = bi_df_m[1:].sum()
    kpi_boxoffice_m = total_m.boxoffice
    kpi_cash_m = total_m.cash
    kpi_increase_m = int(total_m.increase)

    kpi_date = []
    kpi_date_tmp = bi_df['ReportDate'].values.tolist()
    for date_tmp in  kpi_date_tmp:    
        if type(date_tmp) != type("string"):
            kpi_date.append(int(date_tmp.strftime("%Y%m%d")))
        else:
            kpi_date.append(int(date_tmp.replace("-","")))     

    kpi_boxoffice = bi_df['boxoffice'].values.tolist()
    kpi_cash = bi_df['cash'].values.tolist()

    kpi_increase = []
    for increase_tmp in bi_df['increase'].values.tolist():
        kpi_increase.append(int(increase_tmp))

    r.set("kpi_date",json.dumps(kpi_date))
    r.set("kpi_boxoffice",json.dumps(kpi_boxoffice))
    r.set("kpi_cash",json.dumps(kpi_cash))
    r.set("kpi_increase",json.dumps(kpi_increase))
    r.set("kpi_boxoffice_total",round(kpi_boxoffice_total,2))
    r.set("kpi_cash_total",round(kpi_cash_total,2))
    r.set("kpi_increase_total",kpi_increase_total)
    r.set("kpi_boxoffice_m",round(kpi_boxoffice_m,2))
    r.set("kpi_cash_m",round(kpi_cash_m,2))
    r.set("kpi_increase_m",kpi_increase_m)

    bi_conn.close()
    cms_conn.close()

def get_history_vipmember():
    pass

def get_history_monthmember():
    pass

def main():
    get_history_vip()
    data = {}
    data['kpi_date'] = json.loads(r.get("kpi_date"))
    data['kpi_boxoffice'] = json.loads(r.get("kpi_boxoffice"))
    data['kpi_cash'] = json.loads(r.get("kpi_cash"))
    data['kpi_increase'] = json.loads(r.get("kpi_increase"))
    data['kpi_boxoffice_total'] = round(float(r.get("kpi_boxoffice_total"))/10000,2)
    data['kpi_cash_total'] = round(float(r.get("kpi_cash_total"))/10000,2)
    data['kpi_increase_total'] = round(int(r.get("kpi_increase_total"))/10000,2)
    data['kpi_boxoffice_m'] = round(float(r.get("kpi_boxoffice_m"))/10000,2)
    data['kpi_cash_m'] = round(float(r.get("kpi_cash_m"))/10000,2)
    data['kpi_increase_m'] = round(int(r.get("kpi_increase_m"))/10000,2)
    print data

if __name__ == "__main__":
    main()
           
